Configuring AWS and Qrvey for Redshift Connections

The steps below detail how to configure Qrvey to access Redshift clusters for the purpose of loading data into Qrvey for data analysis.

The changes allow Qrvey to access the cluster and also enable the cluster to export data to S3 for access by Qrvey.

Steps

1. Create a new Secret that points to the Redshift cluster.
   • a. Open the AWS console.
   • b. Use the Redshift AWS account.
   • c. Open AWS Secrets Manager.
   • d. Create a new Secret.
   • e. Select Credentials for a Redshift cluster.
   • f. Specify the User name and Password for the Redshift cluster.
   • g. Select the DB Cluster.
   • h. Click Next.
   • i. Enter a Secret name. For example: RedshiftClusterSecret.
   • j. Click Next.
   • k. Optionally configure automatic rotation, if desired.
   • l. Click Next.
   • m. Click Store.
   • n. Note this Secret’s ARN string for later use.

Alternatively, if using Redshift in serverless mode, then:

• a. Open the AWS console.
• b. Use the Redshift AWS account.
• c. Open AWS Secrets Manager.
• d. Create a new Secret.
• e. Select Other type of secret.
• f. Specify Key/value pairs. This is more easily done using the Plaintext feature. Here is an example:

{
"username":"MY_USERNAME",
"password":"MY_PASSWORD",
"engine":"redshift",
"host":"default.MY_ACCOUNT.us-east-1.redshift-serverless.amazonaws.com",
"port":5439,
"workgroupName":"default"
}

• g. Click Next.
• h. Click Store.
• i. Note this Secret’s ARN string for later use.

2. Set permissions for the Qrvey Database lambda.
   • a. Use the Qrvey AWS account.
   • b. Open AWS Identity and Access Management (IAM).
   • c. Click Roles.
   • d. Search for the DB lambda role. It contains the strings DBDatasourcePumpFunction, elastic-view-function-role, and <prefix>TaskExecutionRole. There is one Role for each Qrvey deployment. Select the role applying to the current Qrvey deployment. Note down the ARN to be used later as ROLE_DB_DATASOURCE_PUMP_FUNCTION, ELASTIC_VIEW_FUNCTION_ROLE, and ECS_TASK_EXECUTION_ROLE
   • e. Click Add inline policy.
   • f. Click the JSON tab.
   • g. Paste the policy, replacing AWS_ACCOUNT_REDSHIFT with the AWS account number for the Redshift cluster.

{
    "Version": "2012-10-17",
    "Statement": {
        "Effect": "Allow",
        "Action": "sts:AssumeRole",
        "Resource": [
            "arn:aws:iam::AWS_ACCOUNT_REDSHIFT:role/RedshiftDataAccessRole"
        ]
    }
}

• h. Click Review policy.
• i. Name the policy. Example: RedshiftDataAccessAssumeRole
• j. Click Create Policy. See that the new policy has been added.

3. Add a trust relationship for Qrvey role DbDatasourceExportToS3Role. (During data loads, Qrvey will temporarily move the data to S3.)
   • a. Continue in the Qrvey AWS account, IAM.
   • b. Search for the export role. It contains this string: DbDatasourceExportToS3Role.
   • c. Note the ARN of role DbDatasourceExportToS3Role, to be used later.
   • d. Click the Trust relationships tab.
   • e. Click Edit trust relationship.
   • f. Paste the trust relationship below, replacing the appropriate ARN.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": [
          "arn:aws:iam::AWS_ACCOUNT_REDSHIFT:role/RedshiftDataAccessRole"
        ]
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

• g. Save the trust relationship.

4. Create a new Policy.
   • a. Switch the Redshift AWS account.
   • b. Open AWS Identity and Access Management (IAM)
   • c. Click Policies.
   • d. Click Create policy.
   • e. Select the JSON tab.
   • f. Paste the policy below below, replacing the appropriate ARNs.

• i. Set the Secret ARN generated above, replacing SECRET_ARN.
• ii. Set the Role ARN for the DbDatasourceExportToS3Role, noted above, replacing EXPORT_TO_S3_ARN.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift-data:CancelStatement",
                "redshift-data:DescribeStatement",
                "redshift-data:ExecuteStatement"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetSecretValue"
            ],
            "Resource": [
                "SECRET_ARN"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "sts:AssumeRole"
            ],
            "Resource": [
                "EXPORT_TO_S3_ARN"
            ]
        }
    ]
}

• g. Click Next: Tags.
• h. Click Next: Review.
• i. Name the new Policy. For example: “RedshiftDataAccessPolicy”.

5. Create a new Role

   • a. Continue in the Redshift AWS account, IAM.
   • b. Click Roles.
   • c. Click Create Role.
   • d. Select service Redshift.
   • e. Select Redshift - Customizable.
   • f. Click Next: Permissions.
   • g. Use the filter to find the RedshiftDataAccessPolicy created above.
   • h. Check its box.
   • i. Click Next: Tags.
   • j. Click Next: Review.
   • k. Enter the Role name. For example: RedshiftDataAccessRole.
   • l. Click Create Role.
   • m. Note this Role’s ARN string for later use.

6. Add a Trust Relation to the new Role.

   • a. Search for the lambda roles that contain in the name DBDatasourcePumpFunction and elastic-view-function-role. There is one Role for each Qrvey deployment. Select the role applying to the current Qrvey deployment.
   • b. Click Roles.
   • c. Locate the newly created Role.
   • d. Click the Trust Relations tab.
   • e. Click Edit trust relationship.
   • f. Click the tab Trust relationships, then Edit trust relationship.
   • g. Paste the trust relationship shown below, replacing ROLE_DB_DATASOURCE_PUMP_FUNCTION, ELASTIC_VIEW_FUNCTION_ROLE, and ECS_TASK_EXECUTION_ROLE with the Roles noted in Step 2.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "ROLE_DB_DATASOURCE_PUMP_FUNCTION"
      },
      "Action": "sts:AssumeRole"
    },
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "ELASTIC_VIEW_FUNCTION_ROLE"
      },
      "Action": "sts:AssumeRole"
    },
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "ECS_TASK_EXECUTION_ROLE"
      },
      "Action": "sts:AssumeRole"
    },
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "redshift.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

• h. Click Update Trust Policy.

7. Associate the Redshift cluster with the new Role.

   • a. Go to AWS Redshift.
   • b. Click the desired cluster.
   • c. Click the Properties tab.
   • d. Click the Manage IAM roles button.
   • e. Select Enter ARN.
   • f. Enter the ARN of the newly created Role.
   • g. Click Associate IAM Role.
   • h. Click Save changes.

8. Open the Qrvey application.

   • a. Click Datasets.
   • b. Click Connections.
   • c. Create a new Connection for Redshift.
   • d. Enter the Secret ARN and Role ARN created above.
   • e. Test the new Connection.

Debugging

Connection Test timeout error:

This is an error with the Postgres driver trying to connect to Redshift during the Connection Test.

If Qrvey is in a different account, then a VPC is necessary for DBDatasourcePump

Not authorized to assume IAM Role

Error in Redshift SQL UNLOAD command: ERROR: User 
arn:aws:redshift:us-east-1:790133296469:dbuser:redshift-drdev/awsuser is not authorized to assume IAM 
Role arn:aws:iam::790133296469:role/RedshiftDataAccessRole,arn:aws:iam::790133296469:role/
rouup_dataload_DbDatasourceExportToS3Role.

This error occurs when the trust relationship for the role DbDatasourceExportToS3Role is not set. See step 3 above.